USE [ION_Data]
GO

/****** Object:  Table [dbo].[CacheSummaryReportYear]    Script Date: 08/23/2011 19:14:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CacheSummaryReportYear](
	[ReportYear] [int] NOT NULL,
	[ReportMonth] [int] NOT NULL,
	[StoreName] [nchar](200) NOT NULL,
	[PeekOfTime] [int] NOT NULL,
	[Value] [float] NULL,
 CONSTRAINT [PK_CacheSummaryReportYear] PRIMARY KEY CLUSTERED 
(
	[ReportYear] ASC,
	[ReportMonth] ASC,
	[StoreName] ASC,
	[PeekOfTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

IF OBJECT_ID('dbo.CreateCacheForAED_SummaryDataOfStoreByYear') IS NOT NULL
 DROP PROC dbo.CreateCacheForAED_SummaryDataOfStoreByYear
GO

create procedure dbo.CreateCacheForAED_SummaryDataOfStoreByYear
(
	@year varchar(4)
)
as
begin
	set nocount on
	
	--declare @year varchar(4); set @year = '2012'
	declare @FromDate datetime; set @FromDate = '01/01/' + @year + ' 12:00:00 AM'
	declare @ToDate datetime; set @ToDate = '12/31/' + @year + ' 12:00:00 AM'
	insert into [ION_Data].[dbo].[CacheSummaryReportYear](ReportYear, StoreName, ReportMonth, PeekOfTime, Value)
	select cast(@year as int) ReportYear, G.Name AS StoreName,
		DATEPART(MONTH, B.TimestampUTC) AS ADE_Month,
		dbo.CheckTimeOfUsePeriod( B.TimestampUTC ) AS Peek_Of_Time,
		MAX(A.Value) - MIN(A.Value) DataValue
	from [ION_Data].[dbo].DataLog A
		join [ION_Data].[dbo].DataLogStamp B on A.DataLogStampID = B.ID
		join [ION_Data].[dbo].Source D on B.SourceID = D.ID
		join [ION_Network].[dbo].Device E on D.Name = E.Name
		join [ION_Network].[dbo].Port F on F.ID = E.PortID
		join [ION_Network].[dbo].Gate G on G.ID = F.GateID
	where A.QuantityID = 129
		AND CONVERT(varchar(10), B.TimestampUTC, 102) >= CONVERT(varchar(10), @FromDate, 102)
		AND CONVERT(varchar(10), B.TimestampUTC, 102) <= CONVERT(varchar(10), @ToDate, 102)	
	group by G.Name, G.[Description],
		DATEPART(MONTH, B.TimestampUTC),
		dbo.CheckTimeOfUsePeriod( B.TimestampUTC )
	order by G.Name
	
	set nocount off
end

exec dbo.CreateCacheForAED_SummaryDataOfStoreByYear '2009'

go

exec dbo.CreateCacheForAED_SummaryDataOfStoreByYear '2010'

go

exec dbo.CreateCacheForAED_SummaryDataOfStoreByYear '2011'

go
